#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2023/12/5 20:43
# @Author  : 新猿异码
# @Site    : https://gitee.com/chshao/autotest
# @CSDN    : https://blog.csdn.net/m0_37576542?type=blog
# @File    : DBClient.py
# @Desc    : 模块功能描述
# @Software: PyCharm
import sqlite3


class Field(object):
    field_type = None

    def __init__(self, null=True, index=False, unique=False, column_name=None, default=None, primary_key=False,
                 collation=None, verbose_name=None):
        self.null = null  # 是否可为空
        self.index = index  # 是否支持索引
        self.unique = unique  # 是否唯一
        self.column_name = column_name  # 字段名
        self.default = default  # 默认值
        self.primary_key = primary_key  # 是否为主键
        self.collation = collation  # 排序规则
        self.verbose_name = verbose_name  # 字段别名


class CharField(Field):
    field_type = "VARCHAR"

    def __init__(self, max_length, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.max_length = max_length


class DateTimeField(Field):
    field_type = "DATETIME"
    format_type = "%Y-%m-%d %H:%M:%S"


class BooleanField(Field):
    field_type = "BOOLEAN"


class IntegerField(Field):
    field_type = "INT"


class FloatField(Field):
    field_type = "FLOAT"


class DoubleField(Field):
    field_type = "DOUBLE"


class TextField(Field):
    field_type = "TEXT"


class Model(object):

    def __init__(self, **kwargs):
        self.__data = kwargs
        # 用于存储sql执行结果
        self.__execute_result = []

    @classmethod
    def insert(cls, **params):
        defaults = {}
        for field_name in dir(cls):
            _field = getattr(cls, field_name)
            if isinstance(_field, Field) and _field.default and field_name not in params:
                defaults[field_name] = _field.default
        params.update(defaults)
        return cls(**{"table": cls.__name__.lower(), "func": "insert", "params": params})

    @classmethod
    def delete(cls, **params):
        return cls(**{"table": cls.__name__.lower(), "func": "delete", "params": params})

    @classmethod
    def update(cls, **params):
        return cls(**{"table": cls.__name__.lower(), "func": "update", "params": params})

    @classmethod
    def select(cls, *params):
        fields = [field for field in dir(cls) if getattr(cls, field) in params]
        return cls(**{"table": cls.__name__.lower(), "func": "select", "params": fields})

    @classmethod
    def create_table(cls):
        fields = {}
        for field_name in dir(cls):
            _field = getattr(cls, field_name)
            if isinstance(_field, Field):
                fields[field_name] = _field
        return cls(**{"table": cls.__name__.lower(), "func": "create_table", "params": fields})

    def where(self, **params):
        self.__data["where"] = params
        return self

    def execute(self):
        func = self.__data.pop("func")
        self.__execute_result = getattr(self.Meta.database, func)(**self.__data)
        return self

    def fetchone(self):
        if self.__execute_result:
            return self.__execute_result[0]

    def fetchall(self):
        return self.__execute_result

    def __iter__(self):
        self.__finish = -1
        return self

    def __next__(self):
        self.__finish += 1
        if self.__execute_result:
            if self.__finish >= len(self.__execute_result):
                raise StopIteration
            result = self.__execute_result[self.__finish]
            for k, v in result.items():
                setattr(self, k, v)
            return self
        raise StopIteration


class MysqlBase(object):

    def __init__(self, db_name, host, username, password):
        pass


class SqliteDataBase(object):

    def __init__(self, database, timeout=5, detect_types=0, isolation_level=None, check_same_thread=True):
        self._database = database
        self._timeout = timeout
        self._check_same_thread = check_same_thread
        self._isolation_level = isolation_level
        self._detect_types = detect_types
        self._conn = None
        self._cursor = None

    @property
    def conn(self):
        if self._conn is None:
            self._conn = self.__connect()
        return self._conn

    @property
    def cursor(self):
        if self._cursor is None:
            self._cursor = self.conn.cursor()
        return self._cursor

    @staticmethod
    def dict_factory(cursor, row):
        # 将游标获取的数据处理成字典返回
        d = {}
        for idx, col in enumerate(cursor.description):
            d[col[0]] = row[idx]
        return d

    def __connect(self):
        conn = sqlite3.connect(self._database, timeout=self._timeout, detect_types=self._detect_types,
                               isolation_level=self._isolation_level, check_same_thread=self._check_same_thread)
        conn.row_factory = SqliteDataBase.dict_factory
        return conn

    def create_table(self, **kwargs):
        table = kwargs.pop('table')
        fields_dict = kwargs.pop('params')
        fields = []
        for field_name, _field in fields_dict.items():
            if _field.column_name:
                field_name = _field.column_name
            field_str = f"{field_name} {_field.field_type}"
            max_length = getattr(_field, "max_length", 0)
            if max_length:
                field_str += f"({max_length})"
            if not _field.null:
                field_str += " NOT NULL"
            if _field.primary_key:
                field_str += " PRIMARY KEY"
            if _field.unique:
                field_str += " UNIQUE"
            fields.append(field_str)
        fields_str = ", ".join(fields)
        try:
            self.cursor.execute(f"SELECT * FROM {table}", ())
        except:
            self.cursor.execute(f"CREATE TABLE {table} ({fields_str})")

    def insert(self, **kwargs):
        table = kwargs.pop("table")
        params = kwargs.pop("params")
        keys = []
        values = []
        symbols = []
        for k, v in params.items():
            keys.append(k)
            values.append(v()) if callable(v) else values.append(v)
            symbols.append("?")
        keys_str = ",".join(keys)
        symbols_str = ",".join(symbols)
        sql = f"INSERT INTO {table} ({keys_str}) VALUES ({symbols_str})"
        try:
            self.cursor.execute(sql, tuple(values))
            self.conn.commit()
        except:
            if self._conn:
                self._conn.rollback()

    def update(self, **kwargs):
        table = kwargs.pop("table")
        params = kwargs.pop("params")
        where = kwargs.get("where", {})
        keys = []
        values = []
        for k, v in params.items():
            keys.append(f"{k}=?")
            values.append(v()) if callable(v) else values.append(v)
        keys_str = ",".join(keys)
        sql = f"UPDATE {table} SET {keys_str}"
        if where:
            where_items = where.items()
            values += [v for k, v in where_items]
            sql += (" WHERE " + " AND ".join([f"{k}=?" for k, v in where_items]))
        try:
            self.cursor.execute(sql, tuple(values))
            self.conn.commit()
        except:
            import traceback
            traceback.print_exc()
            if self._conn:
                self._conn.rollback()

    def select(self, **kwargs):
        table = kwargs.pop("table")
        params = kwargs.pop("params")
        where = kwargs.get("where", {})
        select_field = ",".join(params) if params else "*"
        sql = f"SELECT {select_field} FROM {table}"
        where_items = where.items()
        values = [v for k, v in where_items]
        if values:
            sql += (" WHERE "+" AND ".join([f"{k}=?" for k, v in where_items]))
        self.cursor.execute(sql, tuple(values))
        return self.cursor.fetchall()

    def close(self):
        if self._cursor:
            self._cursor.close()
        if self._conn:
            self._conn.close()


if __name__ == '__main__':
    a=[1]
    a+=[2,3]
    print(a)
